package groupby.persistence;

import groupby.model.Category;
import groupby.model.Groupby;
import groupby.util.Pages;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import user.model.UserDetial;

public class GroupbyDaoImpl extends HibernateDaoSupport implements GroupbyDao {

	public void save(Groupby groupby) {
		this.getHibernateTemplate().save(groupby);

	}
	/**
	 * 获得当天列表
	 * @return
	 */
	public List getGroupbyList( String title, String startDate, String endDate,String categoryId,final Pages page,int cityId) {
		
	   String where="";
	   if(startDate!=null&&!startDate.trim().equals(""))
		{
			where+=" groupby.startDate>='"+startDate.trim()+"'";
		}
		if(endDate!=null&&!endDate.trim().equals(""))
		{
			if(where.equals(""))
			   {
			     where+=" groupby.endDate<='"+endDate.trim()+"' ";
			   }else
			   {
				   where+=" and groupby.endDate<='"+endDate.trim()+"' ";
			   }
		}
		if(title!=null&&!title.trim().equals(""))
		{
			if(where.equals(""))
			   {
			    where+=" groupby.title  like '%"+title+"%'";
			   }else
			   {
				   where+=" and groupby.title like '%"+title+"%'";
			   }
		}
		if(categoryId!=null&&!categoryId.trim().equals(""))
		{
			if(where.equals(""))
			   {
			       where+=" groupby.categoryId = "+categoryId;
			   }else
			   {
				   where+=" and groupby.categoryId like "+categoryId;
			   }
		}
		if(cityId!=-1)
		{
			if(where.equals(""))
			   {
			       where+=" groupby.cityId = "+cityId;
			   }else
			   {
				   where+=" and groupby.cityId = "+cityId;
			   }
		}
		if(!where.equals(""))
		{
			where="where "+where+" order by groupby.endDate desc";
		}
		
		final String queryStr="from Groupby groupby  "+where+ " order by groupby.inputDatetime desc" ;
		
		List<Groupby> list=(List<Groupby>)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				int rowCount=getRowCount(queryStr);
				page.setRowCount(rowCount);
				page.setTotalPage(rowCount);
				Query query=session.createQuery(queryStr);
				      query.setFirstResult((page.getCurrentPage()-1)*page.getPageSize()); 
			          query.setMaxResults(page.getPageSize());
			 return query.list();
			}
		});
		return list;
	}
	/**
	 * 获得以前的列表
	 */
	public List getBeforeList(final String id) {
		
		 List list=this.getHibernateTemplate().executeFind(new HibernateCallback(){
				public Object doInHibernate(Session session)throws HibernateException, SQLException {
					SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					 String datetime=sdf.format(new Date());
					 Query query =session.createQuery("from Groupby  groupby where groupby.cityId="+id+" and  groupby.endDate>='"+datetime+"' order by groupby.topCount desc");
					 query.setFirstResult(0); 
			         query.setMaxResults(5);
					 return query.list();
				}
		    	
		    });
		return list;
	}
	public int getRowCount(final String hql)
    {
    	Integer rowCount=0;
    	rowCount=(Integer)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				return session.createQuery(hql).list().size();
			}
    	});
    	return rowCount;
    }
	public int getRowCountForSql(final String hql)
    {
    	Integer rowCount=0;
    	rowCount=(Integer)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				return session.createSQLQuery(hql).list().size();
			}
    	});
    	return rowCount;
    }
	/**
	 * 删除groupby
	 */
	public boolean delete(final String id) {
		try
		{
		this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				      Query query=session.createQuery("delete from Groupby groupby where groupby.id in("+id.trim()+")");
				            query.executeUpdate();
				return null;
			}
			
		});
		}catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		return true;
	}
	/**
	 * 
	 * 获得groupby Cateogory
	 */
	public List getCategory(final Pages pages,final String flag) {
		List<Category> list=this.getHibernateTemplate().executeFind(new HibernateCallback(){

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String queryStr="from Category category where category.sort="+flag;
				
				int rowCount=getRowCount(queryStr);
				pages.setRowCount(rowCount);
				pages.setTotalPage(rowCount);
				pages.setPreNextPage();
				Query query=session.createQuery(queryStr);
				     query.setFirstResult((pages.getCurrentPage()-1)*pages.getPageSize()); 
			         query.setMaxResults(pages.getPageSize());
			 return query.list();
			}
			
		});
		
		return list;
	}
	/**
	 * 添加category
	 * @param category
	 * @return
	 */
	public boolean add(Category category) {
		try
		{
		  this.getHibernateTemplate().save(category);
		  
		}catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		return true;
	}
	/**
	 * 修改category
	 * @param category
	 * @return
	 */
	public boolean edit(Category category) {
		try
		{
		  this.getHibernateTemplate().update(category);
		}catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		return true;
	}
	/**
	 * 删除category
	 */
	public boolean deleteCategory(final String id) {
		try
		{
		this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				      Query query=session.createQuery("delete from Category category where category.id in("+id.trim()+")");
				            query.executeUpdate();
				            query=session.createQuery("update  Groupby groupby  set groupby.categoryId=-1 where groupby.categoryId="+id);
				            query.executeUpdate();
				return null;
			}
		});
		}catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		return true;
	}
	/**
	 * 获得 category select list
	 */
	public List getSelectList(final String flag) {
		List<Category> list=this.getHibernateTemplate().executeFind(new HibernateCallback(){

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String queryStr="from Category category where category.sort="+flag;
				int rowCount=getRowCount(queryStr);
				Query query=session.createQuery(queryStr);
			 return query.list();
			}
			
		});
		
		return list;
	}
	/**
	 * 修改groupby category
	 */
	public boolean updateGroupby(final String id, final String categoryId) {
		  try
		  {
			  this.getHibernateTemplate().execute(new HibernateCallback(){

				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					
					    Query query =session.createQuery("update Groupby groupby set groupby.categoryId="+categoryId+" where groupby.id="+id);
					          query.executeUpdate();
					return true;
				}
				  
			  });
		  }catch(Exception e)
		  {
			  e.printStackTrace();
			  return false;
		  }
		  return true;
		
	}
	/**
	 *  获得单个 category by id
	 */
	public Category findCategoryById(Integer categoryId) {
		Category cagtegory=(Category)  this.getHibernateTemplate().get(Category.class, categoryId);
		return cagtegory;
	}
	/**
	 * 获得所有category list
	 */
	public List getCategoryAll(final int flag) {
		   List<Category> categoryList=this.getHibernateTemplate().executeFind(new HibernateCallback(){
              
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				  Query query =session.createQuery("from Category c where c.sort="+flag);
				return query.list();
			}
		   });
		return categoryList;
	}
    /*
     * 
     * 获得未结束的团购
     */
	public List getGroupbyTodayListAll(final Pages pages,final String cityId,final String categoryId,final String  keyword,final String sortFlag) {
	    List list=this.getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				String sql="";
				if(cityId!=null&&!cityId.equals("") )
				{
					sql=" and groupby.cityId="+cityId;
				}
				if(categoryId!=null&&!categoryId.equals(""))
				{
					sql+=" and groupby.categoryId="+categoryId;
				}
				if(keyword!=null&&!keyword.equals(""))
				{
					sql+=" and groupby.title like '%"+keyword+"%'";
				}
				
				
				if(sortFlag!=null&&!sortFlag.equals(""))
				{
					String[] tempSort=sortFlag.split(",");
					if(tempSort[0].equals("price"))
					{
						sql+=" order by groupby.price "+tempSort[1];	
					}else if(tempSort[0].equals("endDate"))
					{
						sql+=" order by UNIX_TIMESTAMP(groupby.endDate)-UNIX_TIMESTAMP(NOW()) "+tempSort[1];
					}else if (tempSort[0].equals("discountPercent"))
					{
						sql+=" order by  groupby.discountPercent "+tempSort[1];
					}
					
				}
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String datetime=sdf.format(new Date());
				String hql="from Groupby  groupby where groupby.endDate>='"+datetime+"' "+sql+" order by groupby.inputDatetime desc";
				 
				 int rowCount=getRowCount(hql);
					pages.setRowCount(rowCount);
					pages.setTotalPage(rowCount);
					pages.setPreNextPage();
					
				 Query query =session.createQuery(hql);
				 query.setFirstResult((pages.getCurrentPage()-1)*pages.getPageSize()); 
		         query.setMaxResults(pages.getPageSize());
				 return query.list();
			}
	    	
	    });
		return list;
	}
	/**
	 * top
	 */
	public int getTop(final String id) {
		
		  
		  this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				
				Query query=session.createQuery("update Groupby g set g.topCount=g.topCount+1 where g.id="+id);
				 
		          query.executeUpdate();
				return null;
			}
			  
		  });
		  String hql = "select g.topCount from Groupby g where g.id="+id;
		  
		  int cont=Integer.parseInt(this.getHibernateTemplate().find(hql).iterator().next()+"");
		return cont;
	}
	/**
	 * 获得感兴趣的团购
	 */
	public List getLikeGroupby(final String tags,final int city) {
		
		List list=(List)this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				
				String[] tagsStr=tags.replaceAll("\"","").split(",");
				String sql="from Groupby  g where  g.cityId="+city;
				for(int i=0;i<tagsStr.length;i++)
				{
					if(i==0)
					{
						sql+=" and ( g.title like '%"+tagsStr[i]+"%'";
					}else
					{
						sql+=" or  g.title like '%"+tagsStr[i]+"%'";
					}
				}
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				 String datetime=sdf.format(new Date());
				 sql+=" ) and g.endDate>='"+datetime+"'";
				sql+="  ORDER BY g.endDate desc";
				
				Query query=session.createQuery(sql);
				query.setFirstResult(0); 
		         query.setMaxResults(2);
				return query.list();
			}
			
		});
		if(list==null||list.size()==0)
		{
			list=(List)this.getHibernateTemplate().execute(new HibernateCallback(){

				public Object doInHibernate(Session session)throws HibernateException, SQLException {
					
					String sql="from Groupby  groupby where groupby.cityId="+city;
					SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					String datetime=sdf.format(new Date());
					    sql+=" and  groupby.endDate >= '"+datetime+"'";
					    sql+=" order by groupby.inputDatetime desc";
					Query query=session.createQuery(sql);
					query.setFirstResult(0); 
			         query.setMaxResults(2);
					return query.list();
				}
				
			});
		}
		return list;
	}
	/**
	 * 获得单个团购
	 */
	public Groupby criticismGroupbyId(int groupbyId) {
		return (Groupby)this.getHibernateTemplate().get(Groupby.class, groupbyId);
		 
	}
	/**
	 * 获得团购用户的信息
	 */
	public List getGroupbyTodayListAll(final String username,final  Pages pages,
			final String categoryId,final  String sort,final String keyword,final int cityId) {
		List list=(List)this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				String sql="";
			
					sql="from Groupby  groupby where groupby.cityId="+cityId+" and groupby.website='"+username+"'";
				
				if(categoryId!=null&&!categoryId.equals(""))
				{
					sql+=" and groupby.categoryId="+categoryId;
				}
				if(keyword!=null&&!keyword.equals(""))
				{
					sql+=" and groupby.title like '%"+keyword+"%'";
				}
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String datetime=sdf.format(new Date());
				    sql+=" and  groupby.endDate >= '"+datetime+"'";
				
				if(sort!=null&&!sort.equals(""))
				{
					String[] tempSort=sort.split(",");
					if(tempSort[0].equals("price"))
					{
						sql+=" order by groupby.price "+tempSort[1];	
					}else if(tempSort[0].equals("endDate"))
					{
						sql+=" order by UNIX_TIMESTAMP(groupby.endDate)-UNIX_TIMESTAMP(NOW()) "+tempSort[1];
					}else if (tempSort[0].equals("discountPercent"))
					{
						sql+=" order by  groupby.discountPercent "+tempSort[1];
					}
					
				}
				sql+=" order by groupby.inputDatetime desc";
				int rowCount=getRowCount(sql);
				pages.setRowCount(rowCount);
				pages.setTotalPage(rowCount);
				pages.setPreNextPage();
				Query query =session.createQuery(sql);
				query.setFirstResult((pages.getCurrentPage()-1)*pages.getPageSize()); 
		         query.setMaxResults(pages.getPageSize());
				return query.list();
			}
			
		});
		return list;
	}
	/**
	 * 评价团购用户
	 */
	public UserDetial criticismGroupbyUserId(final int groupbyUserId) {
		    UserDetial groupbyUser=(UserDetial)this.getHibernateTemplate().execute(new HibernateCallback(){

				public Object doInHibernate(Session session)throws HibernateException, SQLException {
					Query query =session.createQuery("from UserDetial groupUser groupUser.userId="+groupbyUserId);
					return query.uniqueResult();
				}
		    });
		return groupbyUser;
	}
	/**
	 * 获得往期团购
	 */
	public List oldGroupbyList(final Pages pages,final int cityId,final String website,final String cIdStr) {
		List list =(List)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String datetime=sdf.format(new Date());
				String hql="";
				if(cityId!=-1)
				{
					 hql="select id, input_datetime ,left(input_datetime,10) d from groupby g where  g.end_date < '"+datetime+"' and g.city_id="+cityId+" group by d desc";
				}else
				{
					 hql="select id, input_datetime ,left(input_datetime,10) d from groupby g where  g.end_date < '"+datetime+"'  and g.city_id="+cIdStr+" and g.website like '%"+website+"%' group by d desc";
				}
				
				
				int rowCount=getRowCountForSql(hql);
				pages.setRowCount(rowCount);
				pages.setTotalPage(rowCount);
				pages.setPreNextPage();
				
				Query query =session.createSQLQuery(hql);
				query.setFirstResult((pages.getCurrentPage()-1)*pages.getPageSize()); 
		         query.setMaxResults(pages.getPageSize());
				return query.list();
			}
			
		});
	   return list;
	}
	/**
	 * 获得往期团购
	 */
	public List oldGroupbyListByPage(final String categoryId,final String keyword,final String  startTime,final String endTime,final String sort,final Pages page,final  int cityId,final String username) {
		
		List list =(List)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				String sql="";
				if(username.equals(""))
				{
					sql="from Groupby  groupby where groupby.cityId="+cityId;
				}else
				{
					sql="from Groupby  groupby where groupby.website like '%"+username+"%'";
				}
				
			
			if(categoryId!=null&&!categoryId.equals(""))
			{
				sql+=" and groupby.categoryId="+categoryId;
			}
			if(keyword!=null&&!keyword.equals(""))
			{
				sql+=" and groupby.title like '%"+keyword+"%'";
			}
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String datetime=sdf.format(new Date());
			    sql+=" and  groupby.endDate < '"+datetime+"' and groupby.inputDatetime>='"+startTime+"' and groupby.inputDatetime<='"+endTime+"' ";
			
			if(sort!=null&&!sort.equals(""))
			{
				String[] tempSort=sort.split(",");
				if(tempSort[0].equals("price"))
				{
					sql+=" order by groupby.price "+tempSort[1];	
				}else if(tempSort[0].equals("endDate"))
				{
					sql+=" order by groupby.endDate "+tempSort[1];
				}else if (tempSort[0].equals("discountPercent"))
				{
					sql+=" order by  groupby.discountPercent "+tempSort[1];
				}
				
			}
			
			int rowCount=getRowCount(sql);
			page.setRowCount(rowCount);
			page.setTotalPage(rowCount);
			page.setPreNextPage();
			Query query =session.createQuery(sql);
			 query.setFirstResult((page.getCurrentPage()-1)*page.getPageSize()); 
	         query.setMaxResults(page.getPageSize());
			return query.list();
			}
			
		});
	   return list;
	}
	/**
	 * 获得团购用户的团购活动
	 */
	public List myGroupby(final Pages pages,final String keyword,final String sort,final String username,final int cityId) {
		List list=(List)this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				String sql="";
			
					sql="from Groupby  groupby where groupby.cityId="+cityId+" and groupby.website='"+username+"'";
				
				
				if(keyword!=null&&!keyword.equals(""))
				{
					sql+=" and groupby.title like '%"+keyword+"%'";
				}
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String datetime=sdf.format(new Date());
				    sql+=" and  groupby.endDate >= '"+datetime+"'";
				
				if(sort!=null&&!sort.equals(""))
				{
					String[] tempSort=sort.split(",");
					if(tempSort[0].equals("price"))
					{
						sql+=" order by groupby.price "+tempSort[1];	
					}else if(tempSort[0].equals("endDate"))
					{
						sql+=" order by UNIX_TIMESTAMP(groupby.endDate)-UNIX_TIMESTAMP(NOW()) "+tempSort[1];
					}else if (tempSort[0].equals("discountPercent"))
					{
						sql+=" order by  groupby.discountPercent "+tempSort[1];
					}
					
				}
				
				int rowCount=getRowCount(sql);
				pages.setRowCount(rowCount);
				pages.setTotalPage(rowCount);
				pages.setPreNextPage();
				Query query =session.createQuery(sql);
				 query.setFirstResult((pages.getCurrentPage()-1)*pages.getPageSize()); 
		         query.setMaxResults(pages.getPageSize());
				return query.list();
			}
			
		});
		return list;
	}
	/**
	 * 获得我的往期团购活动
	 */
	public List oldGroupbyListByPage(final String keyword,final  String startTime,
			final String endTime,final  String sort,final  Pages page,final   String username) {
		List list =(List)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				String sql="";
				
				sql="from Groupby  groupby where groupby.website like '%"+username+"%'";
			
			
			if(keyword!=null&&!keyword.equals(""))
			{
				sql+=" and groupby.title like '%"+keyword+"%'";
			}
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String datetime=sdf.format(new Date());
			    sql+=" and  groupby.endDate < '"+datetime+"' and groupby.inputDatetime>='"+startTime+"' and groupby.inputDatetime<='"+endTime+"' ";
			
			if(sort!=null&&!sort.equals(""))
			{
				String[] tempSort=sort.split(",");
				if(tempSort[0].equals("price"))
				{
					sql+=" order by groupby.price "+tempSort[1];	
				}else if(tempSort[0].equals("endDate"))
				{
					sql+=" order by groupby.endDate "+tempSort[1];
				}else if (tempSort[0].equals("discountPercent"))
				{
					sql+=" order by  groupby.discountPercent "+tempSort[1];
				}
				
			}
			
			int rowCount=getRowCount(sql);
			page.setRowCount(rowCount);
			page.setTotalPage(rowCount);
			page.setPreNextPage();
			Query query =session.createQuery(sql);
			 query.setFirstResult((page.getCurrentPage()-1)*page.getPageSize()); 
	         query.setMaxResults(page.getPageSize());
			return query.list();
			}
			
		});
	   return list;
	}
	/**
	 * 获得相同团购
	 */
	public boolean isFindGroupby(final String text,final int cityId) {
		List temp=(List)this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String sql="from Groupby  groupby where groupby.cityId="+cityId+" and groupby.title like '%"+text+"%'";
				Query query =session.createQuery(sql);
				List temp=query.list();
				
				return temp;
			}
			
		});
		if(temp==null||temp.size()==0)
		{
				return true;
		}else
		{
			return false;
		}
		
	}
}
